﻿using System;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using Reviewer.Data.Bases;
using Reviewer.Entities;
using Reviewer.Utilities.Data;
using System.Globalization;
using System.Text;
using System.Data;

namespace Reviewer.Data
{
    /// <summary>
    /// Review repository
    /// </summary>
    [CLSCompliant(false)]
    public class ReviewRepository : RepositoryCrudAlternativeBase<Review, Int32, Int32>
    {
        #region fields (static)
        private const string TABLE_NAME = "V2_Anmeldelse";
        private static readonly CrudColumnInfo columnId = new CrudColumnInfo(0, "Id", MySqlDbType.Int32, true, false, false, true);
        private static readonly CrudColumnInfo columnGameId = new CrudColumnInfo(1, "SpilId", MySqlDbType.Int32, false, true, false);
        private static readonly CrudColumnInfo columnHeader = new CrudColumnInfo(2, "Header", MySqlDbType.VarChar, 255, false, false, true);
        private static readonly CrudColumnInfo columnText = new CrudColumnInfo(3, "Tekst", MySqlDbType.Text, false, false, true);
        private static readonly CrudColumnInfo columnRating = new CrudColumnInfo(4, "Karakter", MySqlDbType.Int32, false, false, true);
        private static readonly CrudColumnInfo columnAuthor = new CrudColumnInfo(5, "Forfatter", MySqlDbType.Int32, false, false, true);
        private static readonly CrudColumnInfo columnDistributor = new CrudColumnInfo(6, "Dis", MySqlDbType.Int32, false, false, true);
        private static readonly CrudColumnInfo columnLastModifiedDate = new CrudColumnInfo(7, "Dato", MySqlDbType.Timestamp, false, false, false);
        #endregion

        #region methods (public)
        public Collection<Review> SelectLatest(int topCount)
        {
            return base.SelectAll(topCount, ReviewRepository.columnLastModifiedDate, SortOrder.Descending);
        }

        public Collection<ReviewSearchResult> Search(string gameTitle, int? gameFormat, int? gameGenre, int? reviewAuthor, int? distributor, int? maxResults)
        {
            StringBuilder commandText = new StringBuilder();
            commandText.Append("SELECT ");
            foreach (CrudColumnInfo column in this.Columns)
            {
                commandText.AppendFormat("R.{0}, ", column.Name);
            }

            commandText.Append("G.SpilId AS GameGameId, G.Titel AS GameTitle, G.Format AS GameFormat, G.Genre AS GameGenre, F.Navn as GameFormatName, GN.Navn AS GameGenreName");
            commandText.AppendFormat(" FROM {0} R", ReviewRepository.TABLE_NAME);
            commandText.AppendFormat(" LEFT OUTER JOIN V2_Spil G ON (R.{0} = G.Id)", ReviewRepository.columnGameId.Name);
            commandText.Append(" LEFT OUTER JOIN V2_GloFormat F ON (G.Format = F.Id)");
            commandText.Append(" LEFT OUTER JOIN V2_GloGenres GN ON (G.Genre = GN.Id)");

            commandText.Append(" WHERE 1=1");

            using (MySqlCommand command = new MySqlCommand())
            {
                if (!String.IsNullOrEmpty(gameTitle))
                {
                    commandText.Append(" AND G.Titel LIKE @GameTitle");
                    command.Parameters.AddWithValue("@GameTitle", gameTitle);
                }

                if (gameFormat.HasValue && gameFormat.Value > 0)
                {
                    commandText.Append(" AND G.Format = @GameFormat");
                    command.Parameters.AddWithValue("@GameFormat", gameFormat.Value);
                }

                if (gameGenre.HasValue && gameGenre.Value > 0)
                {
                    commandText.Append(" AND G.Genre = @GameGenre");
                    command.Parameters.AddWithValue("@GameGenre", gameGenre.Value);
                }

                if (reviewAuthor.HasValue && reviewAuthor.Value > 0)
                {
                    commandText.Append(" AND R." + ReviewRepository.columnAuthor.Name + " = @" + ReviewRepository.columnAuthor.Name);
                    ReviewRepository.columnAuthor.AddParameter(command, reviewAuthor.Value);
                }

                if (distributor.HasValue && distributor.Value > 0)
                {
                    commandText.Append(" AND R." + ReviewRepository.columnDistributor.Name + " = @" + ReviewRepository.columnDistributor.Name);
                    ReviewRepository.columnDistributor.AddParameter(command, distributor.Value);
                }

                commandText.AppendFormat(" ORDER BY R.{0} DESC", ReviewRepository.columnLastModifiedDate.Name);
                if (maxResults.HasValue && maxResults.Value > 0)
                {
                    commandText.AppendFormat(" LIMIT {0}", maxResults.Value);
                }

                command.CommandText = commandText.ToString();

                Collection<ReviewSearchResult> list = new Collection<ReviewSearchResult>();
                using (IDataReader reader = ExecuteReader(command))
                {
                    while (reader.Read())
                    {
                        ReviewSearchResult item = new ReviewSearchResult();
                        item.Id = DataReaderUtility.GetValue(reader, reader.GetOrdinal(ReviewRepository.columnId.Name), 0);
                        item.GameId = DataReaderUtility.GetValue(reader, reader.GetOrdinal(ReviewRepository.columnGameId.Name), 0);
                        item.Header = DataReaderUtility.GetValue(reader, reader.GetOrdinal(ReviewRepository.columnHeader.Name), String.Empty);
                        item.Text = DataReaderUtility.GetValue(reader, reader.GetOrdinal(ReviewRepository.columnText.Name), String.Empty);
                        item.Rating = DataReaderUtility.GetValue(reader, reader.GetOrdinal(ReviewRepository.columnRating.Name), 0);
                        item.Author = DataReaderUtility.GetValue(reader, reader.GetOrdinal(ReviewRepository.columnAuthor.Name), 0);
                        item.Distributor = DataReaderUtility.GetValue(reader, reader.GetOrdinal(ReviewRepository.columnDistributor.Name), 0);
                        item.LastModifiedDate = DataReaderUtility.GetValue(reader, reader.GetOrdinal(ReviewRepository.columnLastModifiedDate.Name), DateTime.MinValue);
                        item.GameGameId = DataReaderUtility.GetValue(reader, reader.GetOrdinal("GameGameId"), Guid.Empty);
                        item.GameTitle = DataReaderUtility.GetValue(reader, reader.GetOrdinal("GameTitle"), String.Empty);
                        item.GameFormat = DataReaderUtility.GetValue(reader, reader.GetOrdinal("GameFormat"), 0);
                        item.GameFormatName = DataReaderUtility.GetValue(reader, reader.GetOrdinal("GameFormatName"), String.Empty);
                        item.GameGenre = DataReaderUtility.GetValue(reader, reader.GetOrdinal("GameGenre"), 0);
                        item.GameGenreName = DataReaderUtility.GetValue(reader, reader.GetOrdinal("GameGenreName"), String.Empty);
                        list.Add(item);
                    }
                }
                return list;
            }
        }
        #endregion

        #region methods (protected)
        internal override CrudColumnInfo OrderByColumn()
        {
            return ReviewRepository.columnLastModifiedDate;
        }

        internal override CrudColumnInfo[] Columns
        {
            get
            {
                return new CrudColumnInfo[]{
                    ReviewRepository.columnId
                    , ReviewRepository.columnGameId
                    , ReviewRepository.columnHeader
                    , ReviewRepository.columnText
                    , ReviewRepository.columnRating
                    , ReviewRepository.columnAuthor
                    , ReviewRepository.columnDistributor
                    , ReviewRepository.columnLastModifiedDate
                };
            }
        }

        internal override object ColumnToValue(CrudColumnInfo info, Review item)
        {
            if (info.Equals(ReviewRepository.columnId))
                return item.Id;
            else if (info.Equals(ReviewRepository.columnGameId))
                return item.GameId;
            else if (info.Equals(ReviewRepository.columnHeader))
                return item.Header;
            else if (info.Equals(ReviewRepository.columnText))
                return item.Text;
            else if (info.Equals(ReviewRepository.columnRating))
                return item.Rating;
            else if (info.Equals(ReviewRepository.columnAuthor))
                return item.Author;
            else if (info.Equals(ReviewRepository.columnDistributor))
                return item.Distributor;
            else if (info.Equals(ReviewRepository.columnLastModifiedDate))
                return item.LastModifiedDate;
            return null;
        }

        protected override string TableName
        {
            get { return ReviewRepository.TABLE_NAME; }
        }

        protected override Review BuildItemFromReader(System.Data.IDataReader reader)
        {
            Review item = new Review();
            if (reader != null)
            {
                item.Id = DataReaderUtility.GetValue(reader, reader.GetOrdinal(ReviewRepository.columnId.Name), 0);
                item.GameId = DataReaderUtility.GetValue(reader, reader.GetOrdinal(ReviewRepository.columnGameId.Name), 0);
                item.Header = DataReaderUtility.GetValue(reader, reader.GetOrdinal(ReviewRepository.columnHeader.Name), String.Empty);
                item.Text = DataReaderUtility.GetValue(reader, reader.GetOrdinal(ReviewRepository.columnText.Name), String.Empty);
                item.Rating = DataReaderUtility.GetValue(reader, reader.GetOrdinal(ReviewRepository.columnRating.Name), 0);
                item.Author = DataReaderUtility.GetValue(reader, reader.GetOrdinal(ReviewRepository.columnAuthor.Name), 0);
                item.Distributor = DataReaderUtility.GetValue(reader, reader.GetOrdinal(ReviewRepository.columnDistributor.Name), 0);
                item.LastModifiedDate = DataReaderUtility.GetValue(reader, reader.GetOrdinal(ReviewRepository.columnLastModifiedDate.Name), DateTime.MinValue);
            }
            return item;
        }
        #endregion
    }
}
